﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
using System.Xml.Linq;

namespace JHQC
{
    public static class SqliteDriver
    {
        static SQLiteConnection connection;
        static SqliteDriver()
        {
            string connectionString = "Data Source=queue.db;Version=3;";
            connection = new SQLiteConnection(connectionString);

        }
        static bool init;
        public static void Init()
        {
            if (!init)
            {
                connection.Open();
                CreateTable();
                init = true;
            }
        }
        static void CreateTable()
        {
            string createTableQuery = "CREATE TABLE IF NOT EXISTS OptItem (Id INTEGER PRIMARY KEY AUTOINCREMENT, QrCode TEXT);";
            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }
        public static void Insert(string qrCode)
        {
            string insertQuery = "INSERT INTO OptItem (QrCode) VALUES (@QrCode);";
            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@QrCode", qrCode);
                command.ExecuteNonQuery();
            }
        }
        public static void Remove(string qrCode)
        {
            string delQuery = "DELETE FROM OptItem WHERE QrCode = @QrCode;";
            using (SQLiteCommand command = new SQLiteCommand(delQuery, connection))
            {
                command.Parameters.AddWithValue("@QrCode", qrCode);
                command.ExecuteNonQuery();
            }
        }
        public static void Clear()
        {
            string delQuery = "DELETE FROM OptItem ;";
            using (SQLiteCommand command = new SQLiteCommand(delQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }
        public static List<OptItem> Query()
        {
            List<OptItem> result = new List<OptItem>();
            string qryQuery = "SELECT QrCode FROM OptItem ORDER BY Id";
            using (SQLiteCommand command = new SQLiteCommand(qryQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var q = reader.GetString(0);
                        result.Add(new OptItem(q));
                    }
                }
            }
            return result;
        }
    }
}
